﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DotNet.JCore.Entity;
using DotNet.Collections;
using DotNet.Extensions;
using DotNet.Utility;
using DotNet.Helper;
using DotNet.Data;

namespace DotNet.JCore.Service
{
    public class HistoryNotCheckDataService
    {
        private JCoreRepository<SyncCarExitStation> repos = new JCoreRepository<SyncCarExitStation>();

        /// <summary>
        /// 获取对象
        /// </summary>
        /// <param name = "id" > 主键 </ param >
        public SyncCarExitStationModel Get(string id)
        {
            var SaftSQL = @"SELECT ID,BusCode, OutTime,OwnerStation ,OwnerStationName,SaleCount,SeatCount, OutNum, Driver1Name, Driver1Tel,
                            LineName,OwnerCompName,RunStatus,Syn_Time,CheckStatus,PointName,PointId,DATEDIFF(MI,OutTime,GETDATE()) AS RunSecond
                        FROM  V_HistoryNotCheckData 
                        WHERE CheckStatus=0 AND ID=@id";
            return repos.Database.Query<SyncCarExitStationModel>(SaftSQL, new { id }).FirstOrDefault();
        }


        /// <summary>
        /// 获取对象分页集合
        /// </summary>
        /// <param name="pageCondition">分页对象</param>
        /// <param name="name">名称关键字</param>
        /// <param name="enabled">有限</param>
        /// <param name="pointId">检查点</param>
        public PageList<SyncCarExitStation> GetPageList(PaginationCondition pageCondition,
            string name, string Point, string Station, DateTime? startDate, DateTime? endDate)
        {
            var sql = @"SELECT ID,BusCode, OutTime,OwnerStation ,OwnerStationName,SaleCount,SeatCount, OutNum, Driver1Name, Driver1Tel,
                            LineName,OwnerCompName,RunStatus,Syn_Time,CheckStatus,PointName,PointId,DATEDIFF(MI,OutTime,GETDATE()) AS RunSecond
                        FROM  V_HistoryNotCheckData 
                        WHERE CheckStatus=0";
            string str_where = " ";
            if (!startDate.HasValue && !endDate.HasValue)
            {
                str_where += " AND OutTime > CAST(GETDATE() as date)";
            }
            else
            {
                if (startDate.HasValue)
                {
                    startDate = startDate.ToDateTime();
                    str_where += " AND OutTime > @StartDate";
                }
                if (endDate.HasValue)
                {
                    endDate = endDate.ToDateTime().AddDays(1);
                    str_where += " AND OutTime < @EndDate";
                }
            }
            if (name.IsNotEmpty() && !ValidateHelper.IsStringRegExMatch(name, true, "[`~!@#$%^&*()+=|{}':;',\\[\\].<>/?~！@#￥%……&*（）——+|{}【】‘；：”“’。，、？]"))
            {
                str_where += " AND BusCode LIKE '%" + name + "%'";
            }
            if (Point.IsNotEmpty())
            {
                str_where += " AND PointId=@PointId";
            }
            if (Station.IsNotEmpty())
            {
                str_where += " AND OwnerStation=@OwnerStation";
            }
            if (pageCondition.OrderName.IsNotEmpty())
            {
                str_where += " ORDER BY " + pageCondition.OrderName + " " + pageCondition.OrderDir;
            }
            else
            {
                str_where += " ORDER BY OutTime ASC";
            }
            var query = repos.Page(pageCondition.PageIndex, pageCondition.PageSize, sql + str_where, new { StartDate = startDate, EndDate = endDate, PointId = Point, OwnerStation = Station });
            return query;
        }

        /// <summary>
        /// 获取启用的对象集合(导出)
        /// </summary>
        /// <param name="name">车牌号</param>
        /// <param name="Point">检查点</param>
        /// <param name="Station">所属客运站</param>
        /// <param name="startDate">起始日期</param>
        /// <param name="endDate">截止日期</param>
        public List<SyncCarExitStation> GetSyncCarExitStationList(string name, string Point, string Station, DateTime? startDate, DateTime? endDate)
        {
            var sql = @"SELECT ID,BusCode, OutTime,OwnerStation ,OwnerStationName,SaleCount,SeatCount, OutNum, Driver1Name, Driver1Tel,
                            LineName,OwnerCompName,RunStatus,Syn_Time,CheckStatus,PointName,PointId,DATEDIFF(MI,OutTime,GETDATE()) AS RunSecond
                        FROM  V_HistoryNotCheckData 
                        WHERE CheckStatus=0";
            string str_where = " ";
            if (!startDate.HasValue && !endDate.HasValue)
            {
                str_where += " AND OutTime > CAST(GETDATE() as date)";
            }
            else
            {
                if (startDate.HasValue)
                {
                    startDate = startDate.ToDateTime();
                    str_where += " AND OutTime > @StartDate";
                }
                if (endDate.HasValue)
                {
                    endDate = endDate.ToDateTime().AddDays(1);
                    str_where += " AND OutTime < @EndDate";
                }
            }
            if (name.IsNotEmpty() && !ValidateHelper.IsStringRegExMatch(name, true, "[`~!@#$%^&*()+=|{}':;',\\[\\].<>/?~！@#￥%……&*（）——+|{}【】‘；：”“’。，、？]"))
            {
                str_where += " AND BusCode LIKE '%" + name + "%'";
            }
            if (Point.IsNotEmpty())
            {
                str_where += " AND PointId=@PointId";
            }
            if (Station.IsNotEmpty())
            {
                str_where += " AND OwnerStation=@OwnerStation";
            }
            str_where += " ORDER BY OutTime ASC";
            return repos.Database.Query<SyncCarExitStation>(sql + str_where, new { StartDate = startDate, EndDate = endDate, PointId = Point, OwnerStation = Station }).ToList();
        }

        #region
        /// <summary>
        /// 
        /// </summary>
        /// <param name="carNO"></param>
        /// <param name="startDate"></param>
        /// <returns></returns>
        public List<SyncCarExitStation> GetList(string carNO, DateTime? startDate)
        {
            var sql = @"SELECT ID,BusCode, OutTime,OwnerStation ,OwnerStationName,SaleCount,SeatCount, OutNum, Driver1Name, Driver1Tel,
                            LineName,OwnerCompName,RunStatus,Syn_Time,CheckStatus,PointName,PointId,DATEDIFF(MI,OutTime,GETDATE()) AS RunSecond
                        FROM  V_HistoryNotCheckData 
                        WHERE CheckStatus=0";
            string str_where = " ";
            if (carNO.IsNotEmpty() && !ValidateHelper.IsStringRegExMatch(carNO, true, "[`~!@#$%^&*()+=|{}':;',\\[\\].<>/?~！@#￥%……&*（）——+|{}【】‘；：”“’。，、？]"))
            {
                str_where += " AND BusCode LIKE '%" + carNO + "%'";
            }
            else
            {
                str_where += " AND BusCode IS NULL";
            }
            if (startDate.HasValue)
            {
                startDate = startDate.ToDateTime();
                str_where += " AND OutTime > @StartDate";
            }
            else
            {
                str_where += " AND OutTime IS NULL";
            }
            str_where += " ORDER BY OutTime ASC";
            return repos.Database.Query<SyncCarExitStation>(sql + str_where, new { StartDate = startDate}).ToList();
        }
        #endregion
    }
}
